﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GDCal.vo;
using System.Collections;
using System.Data.SqlClient;
using System.Data;

namespace GDCal.dao
{
    public class ArcGISServerInfoDao : AbstractDao
    {
        public static ArrayList filter(ArcGISServerInfoVO filterObj)
        {
            ArrayList ret = new ArrayList();
            SqlDataReader rs;
            ArcGISServerInfoVO temp = null;
            SqlConnection oConn = getConnection();

            String whereClause = buildWhereClause(filterObj);
            String strSQL = "SELECT id, name, type, url, host_name, user_name, password FROM ArcGIS_Server_Info";
            if (whereClause != null)
                strSQL += whereClause;
            strSQL += " ORDER BY id ASC";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            while (rs.Read())
            {
                temp = new ArcGISServerInfoVO();
                temp.setId(rs.GetInt32(0));
                temp.setName(rs.GetString(1));
                temp.setType(rs.GetString(2));
                temp.setUrl(rs.GetString(3));
                temp.setHostName(rs.GetString(4));
                temp.setUserName(rs.GetString(5));
                temp.setPassword(rs.GetString(6));
                ret.Add(temp);
            }

            oConn.Close();
            return ret;
        }

        private static String buildWhereClause(ArcGISServerInfoVO filterObj)
        {
            String ret = "";
            Boolean addAnd = false;
            if (filterObj != null)
            {
                if (filterObj.getId() > 0)
                {
                    ret += " id = " + filterObj.getId();
                    addAnd = true;
                }

                if (filterObj.getName() != null && !"".Equals(filterObj.getName()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " name like (N'%" + filterObj.getName() + "%')";
                }

                if (filterObj.getType() != null && !"".Equals(filterObj.getType()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " type like (N'%" + filterObj.getType() + "%')";
                }

                if (filterObj.getUrl() != null && !"".Equals(filterObj.getUrl()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " url like (N'%" + filterObj.getUrl() + "%')";
                }

                if (filterObj.getHostName() != null && !"".Equals(filterObj.getHostName()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " host_name like (N'%" + filterObj.getHostName() + "%')";
                }

                if (filterObj.getUserName() != null && !"".Equals(filterObj.getUserName()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " user_name like (N'%" + filterObj.getUserName() + "%')";
                }

                if (filterObj.getPassword() != null && !"".Equals(filterObj.getPassword()))
                {
                    if (addAnd)
                    {
                        ret += " AND";
                    }
                    addAnd = true;
                    ret += " password like (N'%" + filterObj.getPassword() + "%')";
                }

                if (!"".Equals(ret))
                {
                    ret = " WHERE" + ret;
                }
            }
            return ret;
        }

        public static int delArcGISServerInfo(Int32 delId)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "DELETE FROM ArcGIS_Server_Info WHERE id = " + delId;

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static ArcGISServerInfoVO getArcGISServerInfo(int id)
        {
            SqlDataReader rs;
            ArcGISServerInfoVO ret = null;
            if (id == 0)
            {
                ret = new ArcGISServerInfoVO();
                ret.setId(0);
                ret.setName("");
                ret.setType("");
                ret.setUrl("");
                ret.setHostName("");
                ret.setUserName("");
                ret.setPassword("");
                return ret;
            }
            SqlConnection oConn = getConnection();

            String strSQL = "SELECT name, type, url, host_name, user_name, password FROM ArcGIS_Server_Info WHERE id = " + id + "";
            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                rs = oComd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            if (rs.Read())
            {
                ret = new ArcGISServerInfoVO();
                ret.setId(id);
                ret.setName(rs.GetString(0));
                ret.setType(rs.GetString(1));
                ret.setUrl(rs.GetString(2));
                ret.setHostName(rs.GetString(3));
                ret.setUserName(rs.GetString(4));
                ret.setPassword(rs.GetString(5));
            }

            oConn.Close();
            return ret;
        }

        public static int updateArcGISServerInfo(ArcGISServerInfoVO asiVO)
        {
            int ret;
            SqlConnection oConn = getConnection();

            String strSQL = "UPDATE ArcGIS_Server_Info SET "
                            + "name=(N'" + asiVO.getName() + "'), "
                            + "type=(N'" + asiVO.getType() + "'), "
                            + "url=(N'" + asiVO.getUrl() + "'), "
                            + "host_name=(N'" + asiVO.getHostName() + "'), "
                            + "user_name=(N'" + asiVO.getUserName() + "'), "
                            + "password=(N'" + asiVO.getPassword() + "') "
                            + "WHERE id = " + asiVO.getId();

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }

        public static int insertArcGISServerInfo(ArcGISServerInfoVO asiVO)
        {
            int ret;
            SqlConnection oConn = getConnection();
            String strSQL = "INSERT INTO ArcGIS_Server_Info(name, type, url, host_name, user_name, password) VALUES ("
                            + "(N'" + asiVO.getName() + "'), "
                            + "(N'" + asiVO.getType() + "'), "
                            + "(N'" + asiVO.getUrl() + "'), "
                            + "(N'" + asiVO.getHostName() + "'), "
                            + "(N'" + asiVO.getUserName() + "'), "
                            + "(N'" + asiVO.getPassword() + "') )";

            SqlCommand oComd = new SqlCommand(strSQL, oConn);
            oComd.CommandType = CommandType.Text;

            try
            {
                ret = oComd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            oConn.Close();
            return ret;
        }
    }
}